﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Data;
using MySql.Data.MySqlClient;

using BMS.Models;
using BMS.Utilities;

namespace BMS.Models.DataAccess
{
    public class OrderRepository
    {
        private DatabaseAccess db = new DatabaseAccess();

        public List<OrderModel> GetAllOrders()
        {
            List<OrderModel> allOrders = new List<OrderModel>();
            String query = "SELECT * FROM " + Configuration.DatabaseName + ".order";

            db.DBConnectionOn();

            try
            {
                MySqlCommand cmd = new MySqlCommand(query, db.connection);
                MySqlDataReader dataReader = cmd.ExecuteReader();

                while (dataReader.Read())
                {
                    OrderModel order = new OrderModel();

                    order.FMName = dataReader["vc_fldFMCode"] + "";
                    order.ServiceOrderNo = dataReader["vc_fldServiceOrderNo"] + "";
                    order.Location = dataReader["vc_fldLocation"] + "";
                    order.City = dataReader["vc_fldCity"] + "";
                    order.Description = dataReader["vc_fldDescription"] + "";
                    order.WorkOrderNo= dataReader["vc_fldWorkOrderNo"] + "";
                    order.Emergency = dataReader["vc_fldEmergency"] + "";
                    order.OrderCreationDate = DateTime.Parse(dataReader["dt_fldCreate"] + "");
                    order.IsValid = dataReader["vc_fldValid"] + "";
                    order.QuotationNo = dataReader["vc_fldQuotationNo"] + "";

                    allOrders.Add(order);
                }
            }
            catch (Exception e)
            {
                throw;
            }
            finally
            {
                db.DBConnectionOff();
            }
            return allOrders;
        }

        public OrderModel GetOrder(String quotNo)
        {
            OrderModel foundOrder = new OrderModel();

            List<OrderModel> tempOrderList = GetAllOrders();
            foundOrder = tempOrderList.Find(x => x.QuotationNo == quotNo);

            return foundOrder;
        }

        public OrderModel GetOrderByWONo(String WONo)
        {
            OrderModel foundOrder = new OrderModel();

            List<OrderModel> tempOrderList = GetAllOrders();
            foundOrder = tempOrderList.Find(x => x.WorkOrderNo == WONo);

            return foundOrder;
        }

        public OrderModel GetOrder(String soNo, String fmName)
        {
            OrderModel foundOrder = new OrderModel();

            List<OrderModel> tempOrderList = GetAllOrders();
            foundOrder = tempOrderList.Find(x => x.ServiceOrderNo == soNo && x.FMName == fmName);

            return foundOrder;
        }

        public OrderModel GetOrder(String fmName, DateTime orderDate, String location, String description)
        {
            OrderModel foundOrder = new OrderModel();

            List<OrderModel> tempOrderList = GetAllOrders();
            foundOrder = tempOrderList.Find(x => x.OrderCreationDate == orderDate && x.FMName == fmName
                && x.Location == location && x.Description == description);

            return foundOrder;
        }

        public OrderModel GetOrder(String soNo, String fmName, String quotNo)
        {
            OrderModel foundOrder = new OrderModel();

            List<OrderModel> tempOrderList = GetAllOrders();
            foundOrder = tempOrderList.Find(x => x.ServiceOrderNo == soNo && x.FMName == fmName && x.QuotationNo == quotNo);

            return foundOrder;
        }

        public OrderModel GetOrder(String fmName, String quotNo, DateTime orderDate, String location, String description)
        {
            OrderModel foundOrder = new OrderModel();

            List<OrderModel> tempOrderList = GetAllOrders();
            foundOrder = tempOrderList.Find(x => x.OrderCreationDate == orderDate && x.FMName == fmName 
                && x.QuotationNo == quotNo && x.Location == location && x.Description == description);

            return foundOrder;
        }

        public bool Insert(OrderModel orderModel)
        {
            bool isInserted = false;

            try
            {
                String query = "INSERT INTO " + Configuration.DatabaseName + ".order (vc_fldFMCode, vc_fldServiceOrderNo, vc_fldLocation, vc_fldCity, vc_fldDescription,"
                    + "vc_fldWorkOrderNo, vc_fldEmergency, dt_fldCreate," + "vc_fldValid) VALUES('" + orderModel.FMName + "', '" + orderModel.ServiceOrderNo
                    + "', '" + orderModel.City + "', '" + orderModel.Location + "', '" + orderModel.Description + "', '" + orderModel.WorkOrderNo + "', '" + orderModel.Emergency + "', '"
                    + orderModel.OrderCreationDate.ToString("yyyy-MM-dd H:mm:ss") + "', '" + orderModel.IsValid + "')";
                db.DBConnectionOn();
                //create command and assign the query and connection from the constructor
                MySqlCommand cmd = new MySqlCommand(query, db.connection);

                //Execute command
                cmd.ExecuteNonQuery();

                isInserted = true;
            }
            catch (Exception e)
            {
                isInserted = false;
                throw;
            }
            finally
            {
                db.DBConnectionOff();
            }

            return isInserted;
        }

        public bool Update(OrderModel orderModel)
        {
            bool isUpdated = false;

            try
            {
                String query = "UPDATE " + Configuration.DatabaseName + ".order SET vc_fldQuotationNo = '" + orderModel.QuotationNo 
                    + "', vc_fldWorkOrderNo = '" + orderModel.WorkOrderNo + "' WHERE vc_fldFMCode = '" + orderModel.FMName 
                    + "' and vc_fldServiceOrderNo = '" + orderModel.ServiceOrderNo + "'";
                db.DBConnectionOn();
                //create command and assign the query and connection from the constructor
                MySqlCommand cmd = new MySqlCommand(query, db.connection);

                //Execute command
                cmd.ExecuteNonQuery();

                isUpdated = true;
            }
            catch (Exception e)
            {
                isUpdated = false;
                throw;
            }
            finally
            {
                db.DBConnectionOff();
            }

            return isUpdated;
        }

        public bool Insert(List<OrderModel> orderModelList)
        {
            bool isInserted = false;

            foreach (OrderModel orderModel in orderModelList)
            {
                try
                {
                    String query = "INSERT INTO " + Configuration.DatabaseName + ".order (vc_fldFMCode, vc_fldServiceOrderNo, vc_fldLocation, vc_fldCity, vc_fldDescription,"
                        + "vc_fldWorkOrderNo, vc_fldEmergency, dt_fldCreate," + "vc_fldValid) VALUES('" + orderModel.FMName + "', '" + orderModel.ServiceOrderNo
                        + "', '" + orderModel.Location + "', '" + orderModel.City + "', '" + orderModel.Description + "', '" + orderModel.WorkOrderNo + "', '" + orderModel.Emergency + "', '"
                        + orderModel.OrderCreationDate.ToString("yyyy-MM-dd H:mm:ss") + "', '" + orderModel.IsValid + "')";
                    db.DBConnectionOn();
                    //create command and assign the query and connection from the constructor
                    MySqlCommand cmd = new MySqlCommand(query, db.connection);

                    //Execute command
                    cmd.ExecuteNonQuery();

                    isInserted = true;
                }
                catch (Exception e)
                {
                    isInserted = false;
                    throw;
                }
                finally
                {
                    db.DBConnectionOff();
                }
            }

            return isInserted;
        }
    }
}